﻿using System.Collections.Generic;
using System.Data.SqlClient;
using Abs.Core.Models.User;

namespace Abs.Core.DAL.AdoNet
{
    internal class UserRepository : AdoNetRepository<User>, IAuthenticate<LogIn, User>
    {
        public override bool Create(User item)
        {
            return this.CreateOrUpdate(item, StoredProcedures.CreateUser);
        }//end of public override bool Create(UserManagement item)

        public override IList<User> SelectAll()
        {
            var users = new List<User>();
            this.SqlCommand.CommandText = StoredProcedures.GetUsers;
            var dataReader = this.ExecuteReader();

            while (dataReader.Read())
            {
                users.Add(new User
                            {
                                Email = (string) dataReader["Email"],
                                Name = (string) dataReader["Name"],
                                RoleId = (int) dataReader["RoleId"]
                            });
            }//end of if (dataReader.Read() == true)
            return users;
        }//end of public override IEnumerable<UserManagement> SelectAll()

        /// <summary>
        ///     Get User based on the given Email as primary key.
        /// </summary>
        /// <param name="id">
        ///     Primary Key
        ///     id[0] represents "Email".
        /// </param>
        /// <returns>
        ///     Return an user, if successfully deleting the item.
        ///     Otherwise return null.
        /// </returns>
        public override User Select(object[] id)
        {
            var email = new SqlParameter("Email", id[0]);

            this.SqlCommand.CommandText = StoredProcedures.GetUser;
            this.SqlCommand.Parameters.Add(email);

            //Execute the stored procedures
            var dataReader = this.ExecuteReader();
            //If the next row (record) exist, advance (go) to
            //the next record and retrieve the item.
            //Otherwise return null.
            if (dataReader.Read())
            {
                return new User
                        {
                            Email = (string) dataReader["Email"],
                            Name = (string) dataReader["Name"],
                            RoleId = (int) dataReader["RoleId"]
                        };
            }//end of if (dataReader.Read() == true)
            return null;
        }//end of public override UserManagement Select(object[] id)

        public override bool Update(User item)
        {
            var email = new SqlParameter("Email", item.Email);
            var name = new SqlParameter("Name", item.Name);
            var roleId = new SqlParameter("RoleId", item.RoleId);

            this.SqlCommand.CommandText = StoredProcedures.EditUser;
            this.SqlCommand.Parameters.Add(email);
            this.SqlCommand.Parameters.Add(name);
            this.SqlCommand.Parameters.Add(roleId);

            return this.ExecuteNonQuery();
        }//end of public override bool Update(UserManagement item)

        /// <summary>
        ///     Delete User based on the given Email as primary key.
        /// </summary>
        /// <param name="id">
        ///     Primary Key
        ///     id[0] represents "Email".
        /// </param>
        /// <returns>
        ///     Return true, if successfully deleting the item.
        ///     Otherwise return false.
        /// </returns>
        public override bool Delete(object [] id)
        {
            var email = new SqlParameter("Email", id[0]);

            this.SqlCommand.CommandText = StoredProcedures.DeleteUser;
            this.SqlCommand.Parameters.Add(email);

            //Execute the stored procedures
            return this.ExecuteNonQuery();
        }//end of public override bool Delete(UserManagement item)

        public User LogIn(LogIn item)
        {
            var email = new SqlParameter("Email", item.Email);
            var password = new SqlParameter("Password", item.Password);

            this.SqlCommand.CommandText = StoredProcedures.LogOn;
            this.SqlCommand.Parameters.Add(email);
            this.SqlCommand.Parameters.Add(password);

            var dataReader = this.ExecuteReader();

            if( dataReader == null )
                return null;

            if (dataReader.Read())
            {
                return new User
                {
                    Email = (string) dataReader["Email"],
                    Name = (string) dataReader["Name"],
                    RoleId = (int) dataReader["RoleId"]
                };
            }//end of if (dataReader.Read() == true)
            return null;
        }//end of public User LogIn(LogIn item)

        /// <summary>
        /// Since Create and Update methods are almost identical with
        /// the stored procedure command as exception, therefore
        /// this CreateOrUpdate method is used to avoid redundancy.
        /// </summary>
        /// <param name="item">
        ///     Item to be created or updated
        /// </param>
        /// <param name="command">
        ///     Either Create or Update stored procedure command
        /// </param>
        /// <returns>
        ///     Number of rows affected. 
        ///     Return 0 means, failed. 
        ///     Return > 0, success
        /// </returns>
        protected bool CreateOrUpdate(User item, string command)
        {
            var email = new SqlParameter("Email", item.Email);
            var name = new SqlParameter("Name", item.Name);
            var password = new SqlParameter("Password", item.Password);
            var roleId = new SqlParameter("RoleId", item.RoleId);

            this.SqlCommand.CommandText = command;
            this.SqlCommand.Parameters.Add(email);
            this.SqlCommand.Parameters.Add(name);
            this.SqlCommand.Parameters.Add(password);
            this.SqlCommand.Parameters.Add(roleId);

            return this.ExecuteNonQuery();
        }//end of protected override void CreateUpdateParams(UserManagement item)
    }//end of public class UserRepository
}//end of namespace